CREATE TABLE [dbo].[tb_ServiciosExternos]
(
[idServicioExterno] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[idAgente] [int] NOT NULL,
[NumeroOrden] [int] NOT NULL,
[CargoExterno] [int] NOT NULL,
[Horas] [int] NULL,
[UbicacionExterna] [int] NOT NULL,
[CobraJubilacion] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CobraAsignacionFamiliar] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObraSocial] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UltimaDDJJ] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FechaDDJJ] [datetime] NULL,
[FechaAlta] [datetime] NOT NULL,
[Baja] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FechaBaja] [datetime] NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[BajasLogicastb_ServiciosExternos] ON [dbo].[tb_ServiciosExternos]
FOR UPDATE AS
SET NOCOUNT ON
if update(baja) and (Select baja from inserted)='S' and (Select FechaBaja from inserted) is null BEGIN
	UPDATE tb_ServiciosExternos set FechaBaja = getdate() where idServicioExterno=(Select idServicioExterno from inserted)
END
else
if (update(baja) and (Select baja from inserted)='N')	UPDATE tb_ServiciosExternos set FechaBaja= null where idServicioExterno=(Select idServicioExterno from inserted)

GO
ALTER TABLE [dbo].[tb_ServiciosExternos] ADD CONSTRAINT [PK_tb_ServiciosExternos] PRIMARY KEY CLUSTERED  ([idServicioExterno]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_ServiciosExternos] WITH NOCHECK ADD CONSTRAINT [FK_tb_ServiciosExternos_tb_CargosExternos] FOREIGN KEY ([CargoExterno]) REFERENCES [dbo].[tb_CargosExternos] ([Id])
GO
ALTER TABLE [dbo].[tb_ServiciosExternos] WITH NOCHECK ADD CONSTRAINT [FK_tb_ServiciosExternos_tb_Agentes] FOREIGN KEY ([idAgente]) REFERENCES [dbo].[tb_Agentes] ([idAgente])
GO
ALTER TABLE [dbo].[tb_ServiciosExternos] WITH NOCHECK ADD CONSTRAINT [FK_tb_ServiciosExternos_tb_UbicacionesExternas] FOREIGN KEY ([UbicacionExterna]) REFERENCES [dbo].[tb_UbicacionesExternas] ([idUbicacionExterna])
GO
